package com.bdqn.util;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Excel文件操作工具类，包括读、写、合并等功能
 *
 * @author : luobz
 */
public class PoiExcelUtil {

    private static Logger log = LoggerFactory.getLogger(PoiExcelUtil.class);

    // %%%%%%%%-------常量部分 开始----------%%%%%%%%%
    /**
     * 默认的开始读取的行位置为第一行（索引值为0）
     */
    private final static int READ_START_POS = 0;

    /**
     * 默认结束读取的行位置为最后一行（索引值=0，用负数来表示倒数第n行）
     */
    private final static int READ_END_POS = 0;

    /**
     * 默认Excel内容的开始比较列位置为第一列（索引值为0）
     */
    private final static int COMPARE_POS = 0;

    /**
     * 默认多文件合并的时需要做内容比较（相同的内容不重复出现）
     */
    private final static boolean NEED_COMPARE = true;

    /**
     * 默认多文件合并的新文件遇到名称重复时，进行覆盖
     */
    private final static boolean NEED_OVERWRITE = true;

    /**
     * 默认只操作一个sheet
     */
    private final static boolean ONLY_ONE_SHEET = true;

    /**
     * 默认读取第一个sheet中（只有当ONLY_ONE_SHEET = true时有效）
     */
    private final static int SELECTED_SHEET = 0;

    /**
     * 默认从第一个sheet开始读取（索引值为0）
     */
    private final static int READ_START_SHEET = 0;

    /**
     * 默认在最后一个sheet结束读取（索引值=0，用负数来表示倒数第n行）
     */
    private final static int READ_END_SHEET = 0;

    /**
     * 默认打印各种信息
     */
    private final static boolean PRINT_MSG = true;

    // %%%%%%%%-------常量部分 结束----------%%%%%%%%%

    // %%%%%%%%-------字段部分 开始----------%%%%%%%%%
    /**
     * Excel文件路径
     */
    private String excelPath = "data.xlsx";

    /**
     * 设定开始读取的位置，默认为0
     */
    private int startReadPos = READ_START_POS;

    /**
     * 设定结束读取的位置，默认为0，用负数来表示倒数第n行
     */
    private int endReadPos = READ_END_POS;

    /**
     * 设定开始比较的列位置，默认为0
     */
    private int comparePos = COMPARE_POS;

    /**
     * 设定汇总的文件是否需要替换，默认为true
     */
    private boolean isOverWrite = NEED_OVERWRITE;

    /**
     * 设定是否需要比较，默认为true(仅当不覆写目标内容是有效，即isOverWrite=false时有效)
     */
    private boolean isNeedCompare = NEED_COMPARE;

    /**
     * 设定是否只操作第一个sheet
     */
    private boolean onlyReadOneSheet = ONLY_ONE_SHEET;

    /**
     * 设定操作的sheet在索引值
     */
    private int selectedSheetIdx = SELECTED_SHEET;

    /**
     * 设定操作的sheet的名称
     */
    private String selectedSheetName = "";

    /**
     * 设定开始读取的sheet，默认为0
     */
    private int startSheetIdx = READ_START_SHEET;

    /**
     * 设定结束读取的sheet，默认为0，用负数来表示倒数第n行
     */
    private int endSheetIdx = READ_END_SHEET;

    /**
     * 设定是否打印消息
     */
    private boolean printMsg = PRINT_MSG;

    // %%%%%%%%-------字段部分 结束----------%%%%%%%%%

    public static void main(String[] args) {
        PoiExcelUtil eu = new PoiExcelUtil();

        // 从第一行开始读取
        eu.setStartReadPos(1);

        String src_xlspath = "D:\\2.xls";
        String dist_xlsPath = "D:\\1.xls";
        List<Row> rowList;
        try {
            rowList = eu.readExcel(src_xlspath);
            // eu.writeExcel_xls(rowList, src_xlspath, dist_xlsPath);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public PoiExcelUtil() {

    }

    public PoiExcelUtil(String excelPath) {
        this.excelPath = excelPath;
    }

    /**
     * 还原设定（其实是重新new一个新的对象并返回）
     *
     * @return
     */
    public PoiExcelUtil RestoreSettings() {
        PoiExcelUtil instance = new PoiExcelUtil(this.excelPath);
        return instance;
    }

    /**
     * 自动根据文件扩展名，调用对应的读取方法
     *
     * @return
     * @throws IOException
     */
    public List<Row> readExcel() throws IOException {
        return readExcel(this.excelPath);
    }

    /**
     * 自动根据文件扩展名，调用对应的读取方法
     *
     * @param xlsPath
     * @throws IOException
     * @Title: writeExcel
     * @Date : 2014-9-11 下午01:50:38
     */
    public List<Row> readExcel(String xlsPath) throws IOException {

        // 扩展名为空时，
        if ("".equals(xlsPath)) {
            throw new IOException("文件路径不能为空！");
        } else {
            File file = new File(xlsPath);
            if (!file.exists()) {
                throw new IOException("文件不存在！");
            }
        }

        // 获取扩展名
        String ext = xlsPath.substring(xlsPath.lastIndexOf(".") + 1);

        if ("xls".equals(ext)) {
            // 使用xls方式读取
            return readExcel_xls(xlsPath);
        } else if ("xlsx".equals(ext)) {
            // 使用xlsx方式读取
            return readExcel_xlsx(xlsPath);
        } else { // 依次尝试xls、xlsx方式读取
            out("您要操作的文件没有扩展名，正在尝试以xls方式读取...");
            try {
                return readExcel_xls(xlsPath);
            } catch (IOException e1) {
                out("尝试以xls方式读取，结果失败！，正在尝试以xlsx方式读取...");
                try {
                    return readExcel_xlsx(xlsPath);
                } catch (IOException e2) {
                    out("尝试以xls方式读取，结果失败！\n请您确保您的文件是Excel文件，并且无损，然后再试。");
                    throw e2;
                }
            }
        }
    }

    /**
     * 自动根据文件扩展名，调用对应的写入方法
     *
     * @param rowList
     * @throws IOException
     * @Title: writeExcel
     * @Date : 2014-9-11 下午01:50:38
     */
    public void writeExcel(List<Row> rowList) throws IOException {
        writeExcel(rowList, excelPath);
    }

    /**
     * 自动根据文件扩展名，调用对应的写入方法
     *
     * @param rowList
     * @param xlsPath
     * @throws IOException
     * @Title: writeExcel
     * @Date : 2014-9-11 下午01:50:38
     */
    public void writeExcel(List<Row> rowList, String xlsPath) throws IOException {

        // 扩展名为空时，
        if ("".equals(xlsPath)) {
            throw new IOException("文件路径不能为空！");
        }

        // 获取扩展名
        String ext = xlsPath.substring(xlsPath.lastIndexOf(".") + 1);

        try {

            if ("xls".equals(ext)) {
                // 使用xls方式写入
                writeExcel_xls(rowList, xlsPath);
            } else if ("xlsx".equals(ext)) {
                // 使用xlsx方式写入
                writeExcel_xlsx(rowList, xlsPath);
            } else { // 依次尝试xls、xlsx方式写入
                out("您要操作的文件没有扩展名，正在尝试以xls方式写入...");
                try {
                    writeExcel_xls(rowList, xlsPath);
                } catch (IOException e1) {
                    out("尝试以xls方式写入，结果失败！，正在尝试以xlsx方式读取...");
                    try {
                        writeExcel_xlsx(rowList, xlsPath);
                    } catch (IOException e2) {
                        out("尝试以xls方式写入，结果失败！\n请您确保您的文件是Excel文件，并且无损，然后再试。");
                        throw e2;
                    }
                }
            }
        } catch (IOException e) {
            throw e;
        }
    }

    /**
     * 修改Excel（97-03版，xls格式）
     *
     * @param rowList
     * @param distXlsPath
     * @throws IOException
     * @Title: writeExcel_xls
     * @Date : 2014-9-11 下午01:50:38
     */
    public void writeExcel_xls(List<Row> rowList, String distXlsPath) throws IOException {
        writeExcel_xls(rowList, excelPath, distXlsPath);
    }

    /**
     * 修改Excel（97-03版，xls格式）
     *
     * @param rowList
     * @param srcXlsPath
     * @param distXlsPath
     * @throws IOException
     * @Title: writeExcel_xls
     * @Date : 2014-9-11 下午01:50:38
     */
    public void writeExcel_xls(List<Row> rowList, String srcXlsPath, String distXlsPath) throws IOException {

        // 判断文件路径是否为空
        if (distXlsPath == null || "".equals(distXlsPath)) {
            out("文件路径不能为空");
            throw new IOException("文件路径不能为空");
        }
        // 判断文件路径是否为空
        if (srcXlsPath == null || "".equals(srcXlsPath)) {
            out("文件路径不能为空");
            throw new IOException("文件路径不能为空");
        }

        // 判断列表是否有数据，如果没有数据，则返回
        if (rowList == null || rowList.size() == 0) {
            out("文档为空");
            return;
        }

        try {
            HSSFWorkbook wb = null;

            // 判断文件是否存在
            File file = new File(distXlsPath);
            if (file.exists()) {
                // 如果复写，则删除后
                if (isOverWrite) {
                    file.delete();
                    // 如果文件不存在，则创建一个新的Excel
                    wb = new HSSFWorkbook(new FileInputStream(srcXlsPath));
                } else {
                    // 如果文件存在，则读取Excel
                    wb = new HSSFWorkbook(new FileInputStream(file));
                }
            } else {
                // 如果文件不存在，则创建一个新的Excel
                wb = new HSSFWorkbook(new FileInputStream(srcXlsPath));
            }

            // 将rowlist的内容写到Excel中
            writeExcel(wb, rowList, distXlsPath);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 修改Excel（97-03版，xls格式）
     *
     * @param rowList
     * @param distXlsPath
     * @throws IOException
     * @Title: writeExcel_xls
     * @Date : 2014-9-11 下午01:50:38
     */
    public void writeExcel_xlsx(List<Row> rowList, String distXlsPath) throws IOException {
        writeExcel_xls(rowList, excelPath, distXlsPath);
    }

    /**
     * 修改Excel（2007版，xlsx格式）
     *
     * @param rowList
     * @param srcXlsPath
     * @param srcXlsPath
     * @throws IOException
     * @Title: writeExcel_xlsx
     * @Date : 2014-9-11 下午01:50:38
     */
    public void writeExcel_xlsx(List<Row> rowList, String srcXlsPath, String distXlsPath) throws IOException {

        // 判断文件路径是否为空
        if (distXlsPath == null || "".equals(distXlsPath)) {
            out("文件路径不能为空");
            throw new IOException("文件路径不能为空");
        }
        // 判断文件路径是否为空
        if (srcXlsPath == null || "".equals(srcXlsPath)) {
            out("文件路径不能为空");
            throw new IOException("文件路径不能为空");
        }

        // 判断列表是否有数据，如果没有数据，则返回
        if (rowList == null || rowList.size() == 0) {
            out("文档为空");
            return;
        }

        try {
            // 读取文档
            XSSFWorkbook wb = null;

            // 判断文件是否存在
            File file = new File(distXlsPath);
            if (file.exists()) {
                // 如果复写，则删除后
                if (isOverWrite) {
                    file.delete();
                    // 如果文件不存在，则创建一个新的Excel
                    wb = new XSSFWorkbook(new FileInputStream(srcXlsPath));
                } else {
                    // 如果文件存在，则读取Excel
                    wb = new XSSFWorkbook(new FileInputStream(file));
                }
            } else {
                // 如果文件不存在，则创建一个新的Excel
                wb = new XSSFWorkbook(new FileInputStream(srcXlsPath));
            }
            // 将rowlist的内容添加到Excel中
            writeExcel(wb, rowList, distXlsPath);

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * //读取Excel 2007版，xlsx格式
     *
     * @return
     * @throws IOException
     * @Title: readExcel_xlsx
     * @Date : 2014-9-11 上午11:43:11
     */
    public List<Row> readExcel_xlsx() throws IOException {
        return readExcel_xlsx(excelPath);
    }

    /**
     * //读取Excel 2007版，xlsx格式
     *
     * @return
     * @throws Exception
     * @Title: readExcel_xlsx
     * @Date : 2014-9-11 上午11:43:11
     */
    public List<Row> readExcel_xlsx(String xlsPath) throws IOException {
        // 判断文件是否存在
        File file = new File(xlsPath);
        if (!file.exists()) {
            throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
        }

        XSSFWorkbook wb;
        List<Row> rowList = new ArrayList<>();
        try {
            FileInputStream fis = new FileInputStream(file);
            // 去读Excel
            wb = new XSSFWorkbook(fis);

            // 读取Excel 2007版，xlsx格式
            rowList = readExcel(wb);

        } catch (IOException e) {
            e.printStackTrace();
        }
        return rowList;
    }

    /***
     * 读取Excel(97-03版，xls格式)
     *
     * @throws IOException
     *
     * @Title: readExcel
     * @Date : 2014-9-11 上午09:53:21
     */
    public List<Row> readExcel_xls() throws IOException {
        return readExcel_xls(excelPath);
    }

    /***
     * 读取Excel(97-03版，xls格式)
     *
     * @throws Exception
     *
     * @Title: readExcel
     * @Date : 2014-9-11 上午09:53:21
     */
    public List<Row> readExcel_xls(String xlsPath) throws IOException {

        // 判断文件是否存在
        File file = new File(xlsPath);
        if (!file.exists()) {
            throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
        }

        HSSFWorkbook wb = null;// 用于Workbook级的操作，创建、删除Excel
        List<Row> rowList = new ArrayList<Row>();

        try {
            // 读取Excel
            wb = new HSSFWorkbook(new FileInputStream(file));

            // 读取Excel 97-03版，xls格式
            rowList = readExcel(wb);

        } catch (IOException e) {
            e.printStackTrace();
        }
        return rowList;
    }

    /**
     * 通用读取Excel
     *
     * @param wb
     * @return
     * @Title: readExcel
     * @Date : 2014-9-11 上午11:26:53
     */
    private List<Row> readExcel(Workbook wb) {
        List<Row> rowList = new ArrayList<>();

        // 需要操作的sheet数量
        int sheetCount = 1;

        Sheet sheet = null;
        if (onlyReadOneSheet) {
            // 只操作一个sheet
            // 获取设定操作的sheet(如果设定了名称，按名称查，否则按索引值查)
            sheet = "".equals(selectedSheetName) ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName);
        } else { // 操作多个sheet
            // 获取可以操作的总数量
            sheetCount = wb.getNumberOfSheets();
        }

        // 获取sheet数目
        for (int t = startSheetIdx; t < sheetCount + endSheetIdx; t++) {
            // 获取设定操作的sheet
            if (!onlyReadOneSheet) {
                sheet = wb.getSheetAt(t);
            }
            if (sheet != null) {
                // 获取最后行号
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum > 0) {
                    // 如果>0，表示有数据
                    out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：");
                }

                Row row;
                // 循环读取
                for (int i = startReadPos; i <= lastRowNum + endReadPos; i++) {
                    row = sheet.getRow(i);
                    if (row != null) {
                        rowList.add(row);
                        out("第" + (i + 1) + "行：", false);
                        // 获取每一单元格的值
                        for (int j = 0; j < row.getLastCellNum(); j++) {
                            String value = getCellValue(row.getCell(j));
                            if (!"".equals(value)) {
                                out(value + " | ", false);
                            }
                        }
                        out("");
                    }
                }
            }
        }
        return rowList;
    }

    /**
     * 修改Excel，并另存为
     *
     * @param wb
     * @param rowList
     * @param xlsPath
     * @Title: WriteExcel
     * @Date : 2014-9-11 下午01:33:59
     */
    private void writeExcel(Workbook wb, List<Row> rowList, String xlsPath) {

        if (wb == null) {
            out("操作文档不能为空！");
            return;
        }

        // 修改第一个sheet中的值
        Sheet sheet = wb.getSheetAt(0);

        // 如果每次重写，那么则从开始读取的位置写，否则果获取源文件最新的行。
        int lastRowNum = isOverWrite ? startReadPos : sheet.getLastRowNum() + 1;
        // 记录最新添加的行数
        int t = 0;
        out("要添加的数据总条数为：" + rowList.size());
        for (Row row : rowList) {
            if (row == null) {
                continue;
            }
            // 判断是否已经存在该数据
            int pos = findInExcel(sheet, row);

            // 如果数据行已经存在，则获取后重写，否则自动创建新行。
            Row r = null;
            if (pos >= 0) {
                sheet.removeRow(sheet.getRow(pos));
                r = sheet.createRow(pos);
            } else {
                r = sheet.createRow(lastRowNum + t++);
            }

            // 用于设定单元格样式
            CellStyle newStyle = wb.createCellStyle();

            // 循环为新行创建单元格
            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
                // 获取数据类型
                Cell cell = r.createCell(i);
                // 复制单元格的值到新的单元格
                cell.setCellValue(getCellValue(row.getCell(i)));
                if (row.getCell(i) == null) {
                    continue;
                }
                // 获取原来的单元格样式
                copyCellStyle(row.getCell(i).getCellStyle(), newStyle);
                // 设置样式
                cell.setCellStyle(newStyle);
                //自动跳转列宽度
                sheet.autoSizeColumn(i);
            }
        }
        out("其中检测到重复条数为:" + (rowList.size() - t) + " ，追加条数为：" + t);

        // 统一设定合并单元格
        setMergedRegion(sheet);

        try {
            // 重新将数据写入Excel中
            FileOutputStream outputStream = new FileOutputStream(xlsPath);
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            out("写入Excel时发生错误！ ");
            e.printStackTrace();
        }
    }

    /**
     * 查找某行数据是否在Excel表中存在，返回行数。
     *
     * @param sheet
     * @param row
     * @return
     * @Title: findInExcel
     * @Date : 2014-9-11 下午02:23:12
     */
    private int findInExcel(Sheet sheet, Row row) {
        int pos = -1;

        try {
            // 如果覆写目标文件，或者不需要比较，则直接返回
            if (isOverWrite || !isNeedCompare) {
                return pos;
            }
            for (int i = startReadPos; i <= sheet.getLastRowNum() + endReadPos; i++) {
                Row r = sheet.getRow(i);
                if (r != null && row != null) {
                    String v1 = getCellValue(r.getCell(comparePos));
                    String v2 = getCellValue(row.getCell(comparePos));
                    if (v1.equals(v2)) {
                        pos = i;
                        break;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return pos;
    }

    /**
     * 复制一个单元格样式到目的单元格样式
     *
     * @param fromStyle
     * @param toStyle
     */
    public static void copyCellStyle(CellStyle fromStyle, CellStyle toStyle) {
        toStyle.setAlignment(fromStyle.getAlignmentEnum());
        // 边框和边框颜色
        toStyle.setBorderBottom(fromStyle.getBorderBottomEnum());
        toStyle.setBorderLeft(fromStyle.getBorderLeftEnum());
        toStyle.setBorderRight(fromStyle.getBorderRightEnum());
        toStyle.setBorderTop(fromStyle.getBorderTopEnum());
        toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
        toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
        toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
        toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());

        // 背景和前景
        toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
        toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());

        // 数据格式
        toStyle.setDataFormat(fromStyle.getDataFormat());
        toStyle.setFillPattern(fromStyle.getFillPatternEnum());
        toStyle.setHidden(fromStyle.getHidden());
        // 首行缩进
        toStyle.setIndention(fromStyle.getIndention());
        toStyle.setLocked(fromStyle.getLocked());
        // 旋转
        toStyle.setRotation(fromStyle.getRotation());
        toStyle.setVerticalAlignment(fromStyle.getVerticalAlignmentEnum());
        toStyle.setWrapText(fromStyle.getWrapText());

    }

    /**
     * 获取合并单元格的值
     *
     * @param sheet
     */
    public void setMergedRegion(Sheet sheet) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            // 获取合并单元格位置
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstRow = ca.getFirstRow();
            // 如果第一个合并单元格格式在正式数据的上面，则跳过。
            if (startReadPos - 1 > firstRow) {
                continue;
            }
            int lastRow = ca.getLastRow();
            // 合并的行数
            int mergeRows = lastRow - firstRow;
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            // 根据合并的单元格位置和大小，调整所有的数据行格式，
            for (int j = lastRow + 1; j <= sheet.getLastRowNum(); j++) {
                // 设定合并单元格
                sheet.addMergedRegion(new CellRangeAddress(j, j + mergeRows, firstColumn, lastColumn));
                // 跳过已合并的行
                j = j + mergeRows;
            }

        }
    }

    /**
     * 打印消息，
     *
     * @param msg 消息内容
     */
    private void out(String msg) {
        if (printMsg) {
            out(msg, true);
        }
    }

    /**
     * 打印消息，
     *
     * @param msg 消息内容
     * @param tr 换行
     */
    private void out(String msg, boolean tr) {
        if (printMsg) {
            System.out.print(msg + (tr ? "\n" : ""));
        }
    }

    /**
     * 先查找后创建
     * 创建名称管理器
     *
     * @param wb excel表对象
     * @param name 名称管理器的名称
     * @param expression 对应的数据源 格式为 数据字典!$B$2:$B$20----工作表名+数据
     * @return
     */
    public static Name createName(Workbook wb, String name, String expression) {
        Name refer = wb.getName(name);
        if (refer == null) {
            refer = wb.createName();
        }
        refer.setRefersToFormula(expression);
        refer.setNameName(name);
        return refer;
    }


    /**
     * 设置数据有效性（通过名称管理器级联相关/通过数组）
     * 03版本
     *
     * @param obj 名称管理器/数组
     * @param firstRow 起始行
     * @param endRow 终止行
     * @param firstCol 起始列
     * @param endCol 终止列
     * @return
     */
    public static DataValidation setValidation(Object obj, int firstRow, int endRow, int firstCol, int endCol) {
        // 设置下拉列表的内容
        log.info("起始行:" + firstRow + "___起始列:" + firstCol + "___终止行:" + endRow + "___终止列:" + endCol);
        // 加载下拉列表内容
        DVConstraint constraint;
        if (obj instanceof String[]) {
            String[] nameArr = (String[]) obj;
            constraint = DVConstraint.createExplicitListConstraint(nameArr);
        } else {
            String name = (String) obj;
            constraint = DVConstraint.createFormulaListConstraint(name);
        }
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidation validation = new HSSFDataValidation(regions, constraint);
        // 设置输入信息提示信息
        validation.createPromptBox("下拉选择提示", "请使用下拉方式选择！");
        // 设置输入错误提示信息
        validation.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择！");
        return validation;
    }

    /**
     * 设置数据有效性（通过名称管理器级联相关/通过数组）
     * 通用版
     *
     * @param sheet sheet
     * @param obj 名称管理器/数组
     * @param firstRow 起始行
     * @param endRow 终止行
     * @param firstCol 起始列
     * @param endCol 终止列
     * @return
     */
    public static DataValidation setValidation(Sheet sheet, Object obj, int firstRow, int endRow, int firstCol, int endCol) {
        if (firstRow < 0 || endRow < 0 || firstCol < 0 || endCol < 0 || endRow < firstRow || endCol < firstCol) {
            throw new IllegalArgumentException("Wrong Row or Column index : " + firstRow + ":" + endRow + ":" + firstCol + ":" + endCol);
        }
        DataValidation validation;
        if (sheet instanceof XSSFSheet) {
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
            XSSFDataValidationConstraint dvConstraint;
            if (obj instanceof String[]) {
                String[] nameArr = (String[]) obj;
                dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(nameArr);
            } else {
                String name = (String) obj;
                dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(name);
            }
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
            validation = dvHelper.createValidation(dvConstraint, addressList);
        } else {
            validation = setValidation(obj, firstRow, endRow, firstCol, endCol);
        }
        return validation;
    }

    /**
     * 设置数据有效性（通过日期格式控制范围）
     *
     * @param operatorType 类型 DVConstraint.OperatorType.GREATER_OR_EQUAL
     * @param startDate 开始日期
     * @param endDate 截止日期
     * @param formatStr 日期格式
     * @param firstRow 起始行
     * @param endRow 终止行
     * @param firstCol 起始列
     * @param endCol 终止列
     * @return
     */
    public static DataValidation setValidation(int operatorType, String startDate, String endDate, String formatStr, int firstRow, int endRow, int firstCol, int endCol) {
        // 设置下拉列表的内容
        log.info("起始行:" + firstRow + "___起始列:" + firstCol + "___终止行:" + endRow + "___终止列:" + endCol);
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint.createDateConstraint(operatorType, startDate, endDate, formatStr);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidation validation = new HSSFDataValidation(regions, constraint);
        // 设置输入信息提示信息
        validation.createPromptBox("日期格式提示", "请按照'" + formatStr + "'格式输入日期值！");
        // 设置输入错误提示信息
        validation.createErrorBox("选择错误提示", "你输入的值超出范围或格式与'" + formatStr + "'不符 ，请重新输入！");
        return validation;
    }

    // 根据数据值确定单元格位置（比如：28-AB）

    /**
     * 根据单元格位置定位列（比如：28-AB）
     *
     * @param num
     * @return
     */
    public static String getcellColumnFlag(int num) {
        num++;
        String columnFiled = "";
        int chuNum = 0;
        int yuNum = 0;
        if (num >= 1 && num <= 26) {
            columnFiled = doHandle(num);
        } else {
            chuNum = num / 26;
            yuNum = num % 26 + 1;

            columnFiled += doHandle(chuNum);
            columnFiled += doHandle(yuNum);
        }
        return columnFiled;
    }

    private static String doHandle(final int num) {
        String[] charArr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
        return charArr[num - 1].toString();
    }

    /**
     * 字符串形式返回单元格内容
     *
     * @param hssfCell 单元格
     * @return
     */
    public static String getCellValue(Cell hssfCell) {
        String cellValue = "";
        if (hssfCell != null) {
            DecimalFormat df = new DecimalFormat("#");
            switch (hssfCell.getCellTypeEnum()) {
                case STRING:
                    cellValue = hssfCell.getRichStringCellValue().getString().trim();
                    break;
                case NUMERIC:
                    //日期格式也是以数字类型回传，需要特殊处理
                    if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
                        Date date = hssfCell.getDateCellValue();
                        try {
                            cellValue = DateUtil.format(date, DateUtil.yyyy_MM_dd);
                        } catch (ParseException e) {
                            e.printStackTrace();
                        }
                    } else {
                        cellValue = df.format(hssfCell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    cellValue = String.valueOf(hssfCell.getBooleanCellValue()).trim();
                    break;
                case FORMULA:
                    //不能直接获取公式，应该获取公式对应的内容
                    // cellValue = hssfCell.getCellFormula();
                    try {
                        cellValue = String.valueOf(hssfCell.getNumericCellValue());
                        if ("0.0".equals(cellValue)) {
                            cellValue = "";
                        }
                    } catch (IllegalStateException e) {
                        cellValue = String.valueOf(hssfCell.getRichStringCellValue());
                    }
                    break;
                default:
                    cellValue = "";
            }
        }
        return cellValue.trim();
    }

    public String getExcelPath() {
        return this.excelPath;
    }

    public void setExcelPath(String excelPath) {
        this.excelPath = excelPath;
    }

    public boolean isNeedCompare() {
        return isNeedCompare;
    }

    public void setNeedCompare(boolean isNeedCompare) {
        this.isNeedCompare = isNeedCompare;
    }

    public int getComparePos() {
        return comparePos;
    }

    public void setComparePos(int comparePos) {
        this.comparePos = comparePos;
    }

    public int getStartReadPos() {
        return startReadPos;
    }

    public void setStartReadPos(int startReadPos) {
        this.startReadPos = startReadPos;
    }

    public int getEndReadPos() {
        return endReadPos;
    }

    public void setEndReadPos(int endReadPos) {
        this.endReadPos = endReadPos;
    }

    public boolean isOverWrite() {
        return isOverWrite;
    }

    public void setOverWrite(boolean isOverWrite) {
        this.isOverWrite = isOverWrite;
    }

    public boolean isOnlyReadOneSheet() {
        return onlyReadOneSheet;
    }

    public void setOnlyReadOneSheet(boolean onlyReadOneSheet) {
        this.onlyReadOneSheet = onlyReadOneSheet;
    }

    public int getSelectedSheetIdx() {
        return selectedSheetIdx;
    }

    public void setSelectedSheetIdx(int selectedSheetIdx) {
        this.selectedSheetIdx = selectedSheetIdx;
    }

    public String getSelectedSheetName() {
        return selectedSheetName;
    }

    public void setSelectedSheetName(String selectedSheetName) {
        this.selectedSheetName = selectedSheetName;
    }

    public int getStartSheetIdx() {
        return startSheetIdx;
    }

    public void setStartSheetIdx(int startSheetIdx) {
        this.startSheetIdx = startSheetIdx;
    }

    public int getEndSheetIdx() {
        return endSheetIdx;
    }

    public void setEndSheetIdx(int endSheetIdx) {
        this.endSheetIdx = endSheetIdx;
    }

    public boolean isPrintMsg() {
        return printMsg;
    }

    public void setPrintMsg(boolean printMsg) {
        this.printMsg = printMsg;
    }
}
